package com.hnl.action.talent;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hnl.core.aware.HNLAuthAware;
import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.Talent;
import com.hnl.to.User;
import com.hnl.to.wrapper.TalentWrapper;
import com.hnl.util.DataTablesParamUtility;
import com.hnl.util.JQueryDataTableParamModel;
import com.hnl.util.JSONResponseUtil;

@SuppressWarnings("serial")
@Deprecated
public class LoadTalentByPageReq extends HttpServlet implements HNLAuthAware {
	public static final Logger LOG = LoggerFactory
			.getLogger(LoadTalentByPageReq.class);

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {

		JQueryDataTableParamModel param = DataTablesParamUtility.getParam(req);
		String[] aColumns = { "modify_date", "talent_ID", "e_first_name ",
				"e_last_name", "current_company", "position", "cellphone_1",
				"company_email", "expertise" };
		String sLimit = null;
		String sOrder = null;
		String sWhere = null;
		String sQuery = null;
		int sEcho = 0;

		Collection<ArrayList> talentCollection = new ArrayList<ArrayList>();
		PrintWriter writer = resp.getWriter();

		int colLength = aColumns.length;
		if (param != null) {
			sEcho = param.sEcho;
		}

		// paging
		if (param != null && param.iDisplayStart != -1
				&& param.iDisplayLength != -1) {
			sLimit = " LIMIT " + param.iDisplayStart + ","
					+ param.iDisplayLength;
		}

		// ordering
		sOrder = "ORDER BY ";
		if (param == null || param.iSortColumnIndex == 0) {
			sOrder += "modify_date desc ";
		} else {
			int iSortingCols = param.iSortingCols;
			for (int i = 0; i < iSortingCols; i++) {
				sOrder += aColumns[param.iSortColumnIndex] + " "
						+ param.sSortDirection;
			}
		}

		if (sOrder == "ORDERBY") {
			sOrder = null;
		}

		// filtering
		sWhere = "WHERE(";
		if (param != null && param.sSearch != null) {
			sWhere = "WHERE (";
			for (int i = 0; i < colLength; i++) {
				sWhere += aColumns[i] + " LIKE '%" + param.sSearch + "%' OR ";
			}
			sWhere = sWhere.substring(0, sWhere.length() - 3);
			sWhere += ") AND ";
		}

		if (req.getParameter("loadType").equals("0")) { // personalList
			HttpSession session = ((HttpServletRequest) req).getSession();
			User user = (User) session.getAttribute(ATTRIBUTE_USER);
			sWhere += " modify_user=" + user.getUserID() + " AND ";
		}
		sWhere += "isdelete=false ";

		/* Individual column filtering */
		/*
		 * for(int i=0;i<colLength;i++){
		 * if(req.getParameter("bSearchable_"+i)=="true"
		 * &&req.getParameter("sSearch_"+i)!=null){ if(sWhere==null){
		 * sWhere=" WHERE "; }else{ sWhere=" AND "; }
		 * sWhere+=aColumns[i]+" LIKE '%"+req.getParameter("sSearch_"+i)+"%' ";
		 * } }
		 */

		/*
		 * SQL Queries Get data to display
		 */
		String selectedCols = arrayToString(aColumns, ",");
		sQuery = "SELECT SQL_CALC_FOUND_ROWS " + selectedCols + " FROM TALENT ";

		if (sWhere != null) {
			sQuery += sWhere;
		}
		if (sOrder != null) {
			sQuery += sOrder;
		}
		if (sLimit != null) {
			sQuery += sLimit;
		}

		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			PreparedStatement exeStmt = conn.prepareStatement(sQuery);
			ResultSet rResult = exeStmt.executeQuery();
			exeStmt = conn.prepareStatement("SELECT FOUND_ROWS()");
			ResultSet rResultFilterTotal = exeStmt.executeQuery();
			rResultFilterTotal.next();
			int iFilteredTotal = rResultFilterTotal.getInt(1);

			/* Total data set length */
			sQuery = "SELECT COUNT(talent_ID) FROM TALENT WHERE isdelete=false";
			exeStmt = conn.prepareStatement(sQuery);
			rResultFilterTotal = exeStmt.executeQuery();
			rResultFilterTotal.next();
			int iTotal = rResultFilterTotal.getInt(1);

			while (rResult.next()) {
				Talent talent = new Talent();
				ArrayList<String> talentInfo = new ArrayList<String>();
				talent.seteFirstname(rResult.getString("e_first_name"));
				talent.seteLastname(rResult.getString("e_last_name"));
				talent.setCompany(rResult.getString("current_company"));
				talent.setPosition(rResult.getString("position"));
				talent.setCell1(rResult.getString("cellphone_1"));
				talent.setComMail(rResult.getString("company_email"));
				talent.setExpertise(rResult.getString("expertise"));
				talent.setTalentId(Integer.valueOf(rResult
						.getString("talent_ID")));
				TalentWrapper newTalentWrapper = new TalentWrapper(talent);
				newTalentWrapper.getGUIExpertise();
				talentInfo.add(" "); // add for checkbox
				talentInfo.add(rResult.getString("talent_ID"));
				talentInfo.add(rResult.getString("e_first_name") + " "
						+ rResult.getString("e_last_name"));
				talentInfo.add(rResult.getString("current_company"));
				talentInfo.add(rResult.getString("position"));
				talentInfo.add(rResult.getString("cellphone_1"));
				talentInfo.add(rResult.getString("company_email"));
				talentInfo.add(newTalentWrapper.getGUIExpertise());
				talentCollection.add(talentInfo);
			}

			Map<String, Object> output = new HashMap<String, Object>();
			output.put("sEcho", sEcho);
			output.put("iTotalRecords", iTotal);
			output.put("iTotalDisplayRecords", iFilteredTotal);
			output.put("aaData", talentCollection);

			writer.println(JSONResponseUtil.getJSONObjectString(output));
			// System.out.println(JSONResponseUtil.getJSONObjectString(output));
			rResult.close();
		} catch (SQLException e) {
			LOG.error("Error loading Talent.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
			writer.close();
		}

	}

	private String arrayToString(String[] inputArray, String delimiter) {
		String AsImplodedString;
		if (inputArray.length == 0) {
			AsImplodedString = "";
		} else {
			StringBuffer sb = new StringBuffer();
			sb.append(inputArray[0]);
			for (int i = 1; i < inputArray.length; i++) {
				sb.append(delimiter);
				sb.append(inputArray[i]);
			}
			AsImplodedString = sb.toString();
		}
		return AsImplodedString;
	}
}
